﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections;
using System.Web;
using System.Configuration;
using System.Xml;
using GalaBuilding.Component;
using System.Windows.Forms;
using Manager.FormAdd_Edit;
using log4net;

/// <summary>
/// Summary description for ConnectDB
/// </summary>
public abstract class StandardConnectDB
{


    //Su dung de log
    private static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);


    private SqlConnection _conn;

    public StandardConnectDB()
    {
      //  this._conn = new SqlConnection("Server=" + ConnectDBConfig.ServerName + ";Database=" + ConnectDBConfig.DatabaseName + ";User ID=" + ConnectDBConfig.Uid + ";Password=" + ConnectDBConfig.Pwd + ";Trusted_Connection=False;");

        XmlDocument xmlRed = XML.XMLReader("Connection.xml");
        XmlElement xmlEle = xmlRed.DocumentElement;

        try
        {
            if (xmlEle.SelectSingleNode("costatus").InnerText == "true")
            {
                this._conn = new SqlConnection("Data Source=" + xmlEle.SelectSingleNode("servname").InnerText + ";Initial Catalog=" + xmlEle.SelectSingleNode("database").InnerText + ";Integrated Security=True;");
            }
            else
            {
                this._conn = new SqlConnection("Data Source=" + xmlEle.SelectSingleNode("servname").InnerText + ";Initial Catalog=" + xmlEle.SelectSingleNode("database").InnerText + ";User Id=" + xmlEle.SelectSingleNode("username").InnerText + ";Password=" + xmlEle.SelectSingleNode("password").InnerText + ";");
            }

            Utilities.DatabaseName = xmlEle.SelectSingleNode("database").InnerText;

            //OpenConnection();
        }
        catch
        {
            MessageBox.Show("Lỗi kết nối đến cơ sở dữ liệu! Vui lòng thiết lập lại kết nối!!!", "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);

            Frm_Connection frm = new Frm_Connection();
            frm.ShowDialog();
        }

    }


    public void Connetmaster()
    {
        XmlDocument xmlRed = XML.XMLReader("Connection.xml");
        XmlElement xmlEle = xmlRed.DocumentElement;

        try
        {
            if (xmlEle.SelectSingleNode("costatus").InnerText == "true")
            {
                this._conn = new SqlConnection("Data Source=" + xmlEle.SelectSingleNode("servname").InnerText + ";Initial Catalog=master" + ";Integrated Security=True;");
            }
            else
            {
                this._conn = new SqlConnection("Data Source=" + xmlEle.SelectSingleNode("servname").InnerText + ";Initial Catalog=master" + ";User Id=" + xmlEle.SelectSingleNode("username").InnerText + ";Password=" + xmlEle.SelectSingleNode("password").InnerText + ";");
            }

            Utilities.DatabaseName = "master";

            //OpenConnection();
        }
        catch
        {
            MessageBox.Show("Lỗi kết nối đến cơ sở dữ liệu! Vui lòng thiết lập lại kết nối!!!", "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);

            Frm_Connection frm = new Frm_Connection();
            frm.ShowDialog();
        }
    }
    public StandardConnectDB(string connectionString)
    {
        this._conn = new SqlConnection(connectionString);
    }

    public StandardConnectDB(string ServerName, string DatabaseName, string LoginName, string Password)
    {
        this._conn = new SqlConnection("Server=" + ServerName + ";Database=" + DatabaseName + ";User ID=" + LoginName + ";Password=" + Password + ";Trusted_Connection=False;");
    }

    public StandardConnectDB(string ServerName, string DatabaseName)
    {
        this._conn = new SqlConnection("Server=" + ServerName + ";Database=" + DatabaseName + ";Trusted_Connection=True;");
    }

    private void OpenConnection()
    {
        try
        {
            if (this._conn != null && this._conn.State == ConnectionState.Open)
            {
                this._conn.Close();
            }
            this._conn.Open();
        }
        catch
        {
            MessageBox.Show("Lỗi kết nối đến cơ sở dữ liệu! Vui lòng thiết lập lại kết nối!!!", "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);

            Frm_Connection frm = new Frm_Connection();
            frm.ShowDialog();
        }
    }


    public void CloseConnection1()
    {
         try
        {
            if (this._conn != null && this._conn.State == ConnectionState.Open)
            {
                this._conn.Close();
            }
           // this._conn.Open();
        }
        catch
        {
            MessageBox.Show("Lỗi kết nối đến cơ sở dữ liệu! Vui lòng thiết lập lại kết nối!!!", "ERROR", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);

            Frm_Connection frm = new Frm_Connection();
            frm.ShowDialog();
        }
    }

    private SqlCommand GetCommand(string sp_Name, Dictionary<string, object> paramList)
    {
        SqlCommand cmd = this._conn.CreateCommand();
        try
        {
            cmd.CommandText = sp_Name;
            cmd.CommandType = CommandType.StoredProcedure;
            foreach (KeyValuePair<string, Object> kvp in paramList)
            {
                cmd.Parameters.Add(new SqlParameter(kvp.Key, kvp.Value));
            }
        }
        catch (Exception ex)
        {
            log.Error(ex);
          //  MessageBox.Show(ex.Message);

            return null;
           // throw ex;
        }
        return cmd;
    }

    private SqlCommand GetCommand()
    {
        return this._conn.CreateCommand();
    }

    public DataTable DTExecuteSelect(string query)
    {
       
        DataTable DT = new DataTable();
        try
        {
            OpenConnection();
            SqlDataAdapter adapter = new SqlDataAdapter(query, this._conn);
            adapter.Fill(DT);
        }
        catch (Exception ex)
        {
            log.Error(ex);
            MessageBox.Show("Không thể thực hiện!", "Chú ý", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return null; 
        }
        return DT;
    }

    public DataTable DTExecuteSelect(string sp_Name, Dictionary<string, object> paramList)
    {
        DataTable DT = new DataTable();
        try
        {
            OpenConnection();
            SqlCommand cmd = this.GetCommand(sp_Name, paramList);
            SqlDataAdapter Adapter = new SqlDataAdapter(cmd);
            Adapter.Fill(DT);
        }
        catch (Exception ex)
        {
            log.Error(ex);
            MessageBox.Show("Không thể thực hiện", "Chú ý", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return null; 
        }
        return DT;
    }

    public object ExecuteScale(string query)
    {
        object obj = null;
        try
        {
            OpenConnection();
            SqlCommand cmd = this.GetCommand();
            obj = cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            log.Error(ex);
            MessageBox.Show("Không thể thực hiện", "Chú ý", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return null; 
        }
        return obj;
    }

    public SqlDataReader DRExecuteSelect(string query)
    {
        SqlDataReader reader = null;
        try
        {
            this.OpenConnection();
            SqlCommand cmd = this.GetCommand();
            cmd.CommandText = query;
            reader = cmd.ExecuteReader();
        }
        catch (Exception ex)
        {
            log.Error(ex);
            MessageBox.Show("Không thể thực hiện", "Chú ý", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return null; 
        }
        finally
        {
            //this._conn.Close();
        }
        
        return reader;
    }

    public SqlDataReader DRExecuteSelect(string sp_Name, Dictionary<string, object> paramList)
    {
        SqlDataReader reader = null;
        try
        {
            this.OpenConnection();
            SqlCommand cmd = this.GetCommand(sp_Name, paramList);
            reader = cmd.ExecuteReader();
        }
        catch (Exception ex)
        {
            log.Error(ex);
            MessageBox.Show("Không thể thực hiện", "Chú ý", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return null; 
        }
        finally
        {
            //this._conn.Close();
        }
        return reader;
    }

    public bool ExecuteUpdate(string query)
    {
        bool result = false;
        try
        {
            this.OpenConnection();
            SqlCommand cmd = this.GetCommand();
            cmd.CommandText = query;
            result = cmd.ExecuteNonQuery() > 0;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            this._conn.Close();
        }
        return result;
    }

    public bool ExecuteUpdate(string sp_Name, Dictionary<string, object> paramList)
    {
        bool result = false;
        try
        {//
            this.OpenConnection();
            result = this.GetCommand(sp_Name, paramList).ExecuteNonQuery() > 0;
        }
        catch (Exception ex)
        {
            log.Error(ex);
            MessageBox.Show("Không thể thực hiện", "Chú ý", MessageBoxButtons.OK, MessageBoxIcon.Error);
            return result; 
        }
        finally
        {
            this._conn.Close();
        }
        return result;
    }

    public void CloseConnection()
    {
        if (this._conn != null && this._conn.State == ConnectionState.Open)
        {
            this._conn.Close();
        }
    }

    public int LaySoThuTuTheoNgay(string ngaydi)
    {
        try
        {
            if (_conn.State == ConnectionState.Open) 
                _conn.Close();
            _conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = _conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "spLaySoThuTuTheoNgay";
            comm.Parameters.Add(new SqlParameter("@NgayDat", Convert.ToDateTime(ngaydi)));
            SqlParameter stt = new SqlParameter("@stt", SqlDbType.Int);
            stt.Direction = ParameterDirection.Output;
            comm.Parameters.Add(stt);
            int sott = 0;
            comm.ExecuteNonQuery();
            sott = (int)comm.Parameters["@stt"].Value;
            return sott + 1;
        }
        catch (Exception ex)
        {

           log.Error(ex);
            return -1;
        }
    }
    //getter, setter..
    public SqlConnection Conn
    {
        get { return _conn; }
    }



    public int call_procedure_P(string SPname, SqlParameter[] parameters)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = SPname;
            for (int i = 0; i < parameters.Length; i++)
            {
                cmd.Parameters.Add(parameters[i]);
            }
            cmd.Connection = this._conn;
            OpenConnection();
            int sodong = cmd.ExecuteNonQuery();
            return sodong;
        }
        catch
        {
            return -1;
        }
    }

    public void call_procedure_P_noreturn(string SPname, SqlParameter[] parameters)
    {
        try
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = SPname;
            for (int i = 0; i < parameters.Length; i++)
            {
                cmd.Parameters.Add(parameters[i]);
            }
            cmd.Connection = this._conn;
            OpenConnection();
            cmd.ExecuteNonQuery();

        }
        catch (Exception a)
        {
            MessageBox.Show(a.Message);
        }





    }




    public byte[] encryptDataMD5(string data)
    {
        System.Security.Cryptography.MD5CryptoServiceProvider md5Hasher = new System.Security.Cryptography.MD5CryptoServiceProvider();
        byte[] hashedBytes;
        System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
        hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(data));
        return hashedBytes;
    }

}